﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
using System.Drawing.Imaging;
using System.Diagnostics;
using System.Configuration;
using System.Data.Common;
using System.Data;
using System.Drawing;
using System.Data.SqlServerCe;

namespace GpSpotMe.Ign.TileModel
{
    class DirectTileManager : ITileSource
    {
        private static string IBERPIX_URL = "http://ts0.iberpix.ign.es/tileserver/n=mapa_mtn25;z=30;r=4000;i={0};j={1}.jpg";
        private static string CONNECTION_STRING = ("Data Source =" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\TileData\\IGNtiles.sdf;"));


        public Tile GetTile(int i, int j)
        {
            if (IsTilePresentBBDD(i, j))
            {
                return GetTileFromDDBBDirect(i, j);
            }
            else
            {
                return GetTileFromIGN(i, j);
            }
        }

        private static Tile GetTileFromIGN(int i, int j)
        {
            Tile tile;
            WebResponse response = null;
            try
            {

                System.Uri uriIgn = new Uri(string.Format(IBERPIX_URL, i, j));
                HttpWebRequest request = (HttpWebRequest)WebRequest.Create(uriIgn.AbsoluteUri);
                request.Timeout = 30000;
                response = request.GetResponse();
                Bitmap bmp = new Bitmap(response.GetResponseStream());
                tile = new Tile(i, j, bmp);
                SaveTileInDDBBDir(tile);
            }
            catch (Exception e)
            {
                Bitmap bmp = new Bitmap(256, 256);
                using (Graphics errorGraphics = Graphics.FromImage(bmp))
                {
                    errorGraphics.FillRectangle(new SolidBrush(Color.Coral), 0, 0, bmp.Width, bmp.Height);
                    Brush myBrush = new SolidBrush(Color.Red);
                    Font myFont = new Font(FontFamily.GenericSansSerif, 8, FontStyle.Regular);
                    RectangleF layoutRectangle = new RectangleF(1, 1, 254, 254);
                    errorGraphics.DrawString(e.Message, myFont, myBrush, layoutRectangle);
                }
                tile = new Tile(i, j, bmp);
            }
            finally
            {
                if (response != null) response.Close();
            }
            return tile;
        }

        private static Tile GetTileFromDDBBDirect(int i, int j)
        {
            try
            {
                DateTime start = DateTime.Now;
                using (DbConnection connection = new SqlCeConnection())
                {
                    connection.ConnectionString = CONNECTION_STRING;

                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText =
                            @"SELECT     COORD_X, COORD_Y, TILE
                            FROM         IGN25
                            WHERE     (COORD_X = @i) AND (COORD_Y = @j)";
                        DbParameter p1 = command.CreateParameter();
                        p1.ParameterName = "i";
                        p1.Value = i;
                        command.Parameters.Add(p1);
                        DbParameter p2 = command.CreateParameter();
                        p2.ParameterName = "j";
                        p2.Value = j;
                        command.Parameters.Add(p2);

                        connection.Open();
                        using (DbDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                int x = (int)reader["COORD_X"];
                                int y = (int)reader["COORD_y"];
                                byte[] tile = (byte[])reader["TILE"];
                                Bitmap bmp = null;
                                using (MemoryStream ms = new MemoryStream(tile))
                                {
                                    bmp = new Bitmap(ms);
                                }
                                return new Tile(i, j, bmp);
                            }
                            else
                            {
                                return null;
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

        }

        private static void SaveTileInDDBBDir(Tile til)
        {
            try
            {
                DateTime start = DateTime.Now;
                using (SqlCeConnection connection = new SqlCeConnection())
                {
                    connection.ConnectionString = CONNECTION_STRING;
                    using (SqlCeCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText =
                            @"INSERT INTO IGN25  (COORD_X, COORD_Y, TILE, DATE)  VALUES ( @i, @j, @tile, @date)";
                        DbParameter p1 = command.CreateParameter();
                        p1.ParameterName = "i";
                        p1.Value = til.Coord_I;
                        command.Parameters.Add(p1);
                        DbParameter p2 = command.CreateParameter();
                        p2.ParameterName = "j";
                        p2.Value = til.Coord_J;
                        command.Parameters.Add(p2);
                        SqlCeParameter p3 = new SqlCeParameter("tile", SqlDbType.Image);
                        using (MemoryStream ms = new MemoryStream())
                        {
                            til.Bmp.Save(ms, ImageFormat.Jpeg);
                            byte[] bitmapData = ms.ToArray();
                            p3.Value = bitmapData;
                        }
                        command.Parameters.Add(p3);
                        DbParameter p4 = command.CreateParameter();
                        p4.ParameterName = "date";
                        p4.Value = DateTime.Now;
                        command.Parameters.Add(p4);

                        connection.Open();
                        int n = command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        private static bool IsTilePresentBBDD(int i, int j)
        {
            try
            {
                using (DbConnection connection = new SqlCeConnection())
                {
                    connection.ConnectionString = CONNECTION_STRING;
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText =
                            @"SELECT COUNT(1) FROM    IGN25 WHERE (COORD_X = @i) AND (COORD_Y = @j)";
                        DbParameter p1 = command.CreateParameter();
                        p1.ParameterName = "i";
                        p1.Value = i;
                        command.Parameters.Add(p1);
                        DbParameter p2 = command.CreateParameter();
                        p2.ParameterName = "j";
                        p2.Value = j;
                        command.Parameters.Add(p2);

                        connection.Open();
                        int nt = (int)command.ExecuteScalar();
                        return nt == 1;
                    }
                }
            }
            catch (Exception e)
            {

                throw e;
            }

        }

    }
}
